IF EXISTS (SELECT 1 FROM sysobjects where name='WatchlistView' and type='v')
DROP VIEW WatchlistView
GO

--SELECT * FROM WatchlistView
CREATE VIEW WatchlistView
AS
SELECT DISTINCT Watchlist.PKey
	,em.PKey as EntertainmentMainPKey
	,em.Title+' ('+CONVERT(varchar,ReleaseYear)+')'
		+case when COALESCE(dm.Name,'')<>'' then CHAR(13)+CHAR(10)+'Dir.: '+COALESCE(dm.Name,'') else '' end as TitleDirector
	,em.RunTimeMins as "RunTime"
	,CONVERT(varchar,COALESCE(case when ISNUMERIC(em.IMDbRating)=1 then em.IMDbRating end,'--'))+'/'
		+CONVERT(varchar,COALESCE(case when ISNUMERIC(em.CustomRating)=1 then em.CustomRating end,'--')) as "Rating"
	,2*COALESCE(Watchlist.tmpDateAdded,0)-COALESCE(Watchlist.tmpDateWatched,0) as "Seen"
	,REPLACE(COALESCE(CONVERT(varchar,Watchlist.DateAdded,106),'--'),' ',' ') as DateAdded
	,REPLACE(COALESCE(CONVERT(varchar,Watchlist.DateWatched,106),'--'),' ','-') as DateWatched

FROM EntertainmentMain em
LEFT OUTER JOIN MuveeDirectorRel rel LEFT OUTER JOIN PeopleMain dm ON dm.PKey=rel.PeopleMainPKey AND dm.Status<>'d' AND rel.Status<>'d' ON em.PKey=rel.MuveeMainPKey AND em.Status<>'d'
INNER JOIN
(
	SELECT w1.PKey
		,w1.EntertainmentMainPKey
		,CASE WHEN DateAdded IS NOT NULL THEN 1 ELSE 0 END as tmpDateAdded
		,CASE WHEN DateWatched IS NOT NULL THEN 1 ELSE 0 END as tmpDateWatched
		,DateAdded
		,DateWatched
	FROM WatchlistMain w1
	WHERE Status<>'d'
	and w1.PKey IN
	(
		SELECT TOP 1 PKey
		FROM WatchlistMain w2
		WHERE Status<>'d'
		and w1.EntertainmentMainPKey=w2.EntertainmentMainPKey
		ORDER BY PKey DESC
	)
) Watchlist ON Watchlist.EntertainmentMainPKey=em.PKey and em.Status<>'d'

WHERE (1=1)
	and Watchlist.tmpDateWatched=0
	and Watchlist.DateAdded IS NOT NULL
	and Watchlist.DateWatched IS NULL

/* --reference
select top 1 103,convert(varchar,DateAdded,103) from WatchlistMain
	union
select top 1 105,convert(varchar,DateAdded,105) from WatchlistMain
	union
select top 1 106,convert(varchar,DateAdded,106) from WatchlistMain
	union
select top 1 110,convert(varchar,DateAdded,110) from WatchlistMain
	union
select top 1 111,convert(varchar,DateAdded,111) from WatchlistMain
	union
select top 1 112,convert(varchar,DateAdded,112) from WatchlistMain
*/

